# MSSQL, default DB "pubs". In CfgServer create DB Access Point for "pubs", Table Access objects for tables 
# "authors", "sales", "discounts". Can serve as Log Tables (do not require format).
# In python configuration create Table Access objects with names PubsTableDiscounts, PubsTableAuthors, PubsTableSales,
# set reference to corresponding table access objects in CfgServer. Set table type to "Other",
# try the following tests.

def pubs_tables_test_00():
  result = PubsTableAuthors.ExecSQL("SELECT count(*) FROM authors")

def pubs_tables_test_0():
  result = PubsTableAuthors.ExecSQL("select au_id, au_fname, au_lname from authors where city = \"Oakland\"")  #any SQL statement, be careful with quotes
  

def pubs_tables_test_1():
  result = PubsTableAuthors.RetrieveListOfRecords()        #select * from authors
  PubsTableAuthors.printLog("\nNumber of records in table authors = %s" %str(len(result)))
  PubsTableAuthors.InsertListOfRecords([{"contract": "1", "phone": "506 674-6419", "au_id": "998-72-3569","address": "", 
                                        "au_lname": "Jordan", "zip": "94015", "state": "", "city": "", "au_fname": "Ronald"}])
  result = PubsTableAuthors.RetrieveListOfRecords()        #select * from authors
  PubsTableAuthors.printLog("\nNumber of records in table authors = %s" %str(len(result)))
  PubsTableAuthors.DeleteListOfRecords([{"au_fname": "Ronald", "au_lname": "Jordan"}])     # delete inserted record
  result = PubsTableAuthors.RetrieveListOfRecords()        #select * from authors
  PubsTableAuthors.printLog("\nNumber of records in table authors = %s" %str(len(result)))  
                                        
  
def pubs_tables_test_2():
  result = PubsTableDiscounts.RetrieveListOfRecords({"highqty": 1000})
  PubsTableDiscounts.TestRecord(result[0], {"discounttype": "Volume Discount", "stor_id": "", 
                                "highqty": "1000", "discount": "6.70"})            #exactly 
  PubsTableDiscounts.TestRecord(result[0], {"discounttype": "Volume Discount", "stor_id": "", 
                                "highqty": "1000", "discount": "6.30", "lowqty":"100"})            #wrong field discount, should be error                    

  
def pubs_tables_test_3():
 
  PubsTableSales.InsertListOfRecords([{"qty": "10", "stor_id": "6380", "title_id": "BU1032", 
                                       "payterms": "Net 60", "ord_num": "6E34d", "ord_date": "09/15/00"}])  # insert record
  result = PubsTableSales.RetrieveListOfRecords({"stor_id": "6380", "ord_num": "6E34d"})                    #retrieve this record
  PubsTableSales.TestRecord(result[0], {"qty": "10", "stor_id": "6380", "title_id": "BU1032",               #and test it
                                       "payterms": "Net 60", "ord_num": "6E34d", "ord_date": "09/15/00"})   # date will have another representation, error will occur
  PubsTableSales.TestRecord(result[0], {"qty": "10", "stor_id": "6380", "title_id": "BU1032", 
                                       "payterms": "Net 60", "ord_num": "6E34d", "ord_date": "Sep 15 2000 12:00AM"}) # no error 
  PubsTableSales.DeleteListOfRecords([{"stor_id": "6380", "ord_num": "6E34d"}])                             #delete inserted record         






def pubs_tables_test_memleak():
  for i in range(3500):
    result = PubsTableAuthors.RetrieveListOfRecords()        #select * from authors
  raw_input()

